﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using StillVoiceCommon;
using TN5Y;
using System.Data;
using System.Text;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;

namespace TeamTime.Classes
{
    public class TimeEntry : DataTableRow

    {
        public string CompanyProjectTask;
        public string CompanyName;
        public new void load(string id)
        {
            base.load(id);
            System.Data.SqlClient.SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                "select * from ViewTimeEntries where ID = " + @id,
                new SqlParameter("id", id));
            if (dr.Read())
            {
                CompanyName = dr["CompanyName"].ToString();
                CompanyProjectTask = dr["CompanyProjectTask"].ToString();
            }
            dr.Close();
            dr = null;
        }
        public TimeEntry()
        {
            this.initClass(Utilities.DBConnectionString, "TimeEntry");
        }
    }
    public class TimeEntries
    {
        //internal DataSet search(string searchFor,DateTime fromDate,DateTime toDate,bool check,int projectID, int staffMemberID)
        internal DataSet search(string searchFor, DateTime fromDate, DateTime toDate, bool check, int projectID, int staffMemberID, 
            string sortExpression, string status)
        {
            StringBuilder sql = new StringBuilder("SELECT * FROM ViewTimeEntries where 1=1 ");
            if (searchFor != "")
            {
                sql.Append(" and (CompanyProjectTask like '%'+@searchFor+'%' ");
                if (check == true)
                {
                    sql.Append(" or TimeEntryDescription like '%'+@searchFor+'%' ");
                }
                sql.Append(" or cast(ID as varchar(10)) = @searchFor ");
                sql.Append(" )");
            }
                if (fromDate != null)
                {
                    sql.Append(" and (StartDate >= @FromDate or EndDate >= @FromDate) ");
                }
                if(toDate != null)
                {
                    sql.Append(" and (EndDate <=  @ToDate or EndDate <= @ToDate) ");
                }
                if (projectID > 0)
                {
                    sql.Append(" and ProjectID = @ProjectID ");
                }
                if (staffMemberID > 0)
                {
                    sql.Append(" and StaffMemberID = @staffMemberID ");
                }
                if(status == "Invoiced")
                {
                    sql.Append(" and (InvoiceNumber is not null) ");
                }
                if (status == "Uninvoiced")
                {
                    sql.Append(" and (InvoiceNumber is null) ");
                }

                if (sortExpression != null)
                {
                    sql.Append("  order by " + sortExpression);
                }
                
                
            
                return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql.ToString(),
                                                    new SqlParameter("searchFor", searchFor),
                                                    new SqlParameter("FromDate", fromDate),
                                                    new SqlParameter("ToDate", toDate),
                                                    new SqlParameter("ProjectID", projectID),
                                                    new SqlParameter("staffMemberID", staffMemberID));
                                     
        }

        internal DataSet getTimeSheetEntries(DateTime fromDate, DateTime toDate, Int32 companyID, int staffMemberID, 
                                                string invoiceNum, string status)
        {
            StringBuilder sql = new StringBuilder(" SELECT FullName as StaffMember, StartDate,EndDate, StartTime,FullName, EndTime, Duration, "
            + " case when TaskName=TimeEntryDescription then TaskName else TaskName + ': ' +TimeEntryDescription end as TimeEntryDescription, "
            + " ProjectName, InvoiceNumber, CustomerRefNumber, URL, CompanyName "
            +" FROM dbo.ViewTimeEntries ");
            sql.Append(" where 1=1 ");
            if (fromDate != null)
            {
                sql.Append(" and StartDate >= @FromDate");
            }
            if (toDate != null)
            {
                sql.Append(" and EndDate <=  @ToDate");
            }
            if (staffMemberID > 0)
            {
                sql.Append(" and StaffMemberID = @StaffMemberID ");
            }
            if (companyID > 0)
            {
                sql.Append(" and CompanyID = +@companyID ");
            }
            if (status=="Invoiced")
                sql.Append(" and InvoiceNumber is not null ");
            if (status == "Uninvoiced")
                sql.Append(" and InvoiceNumber is null ");

            if (invoiceNum != "")
                sql.Append(" and InvoiceNumber like '%'+@invoiceNumber+'%'  ");
            sql.Append(" order by StartDate,StartTime ");

            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql.ToString(),
                new SqlParameter("FromDate",fromDate),
                new SqlParameter("ToDate",toDate),
                new SqlParameter("StaffMemberID", staffMemberID),
                new SqlParameter("companyID",companyID),
                new SqlParameter("invoiceNumber", invoiceNum));   
        }






        internal int getTaskIDFromDescription(string p)
        {
            int ret = -1;
            string sql = "select ID from ViewTasks where CompanyProjectTask = @CompanyProjectTask";

           // SqlDataReader dr;
            using (SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                 new SqlParameter("CompanyProjectTask", p)))
            {
                if (dr.Read())
                {
                    ret = Convert.ToInt32(dr[0].ToString());
                }
                dr.Close();
            }
            return ret;
        }
    }
}